package com.crm.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.crm.customers.model.Contract;
import com.crm.customers.model.ContractMoneylog;

public final class XLSUtil {
	
	 public static byte[] downAchievement(List<Contract> rc,List<Contract>  qy) throws IOException{
		    HSSFWorkbook wb = new HSSFWorkbook();
	        HSSFSheet sheet = wb.createSheet("单位业绩表");
	        HSSFSheet sheetone = wb.createSheet("个人业绩表");
	        String names[] = new String[]{"签约时间","姓名","单位","个人业绩","总业绩","备注","转入公司账户名称","到账日期","单位打款账户名称","是否开发票收据","签约"};
	        String namestwo[] = new String[]{"单位名称","所签建造师姓名","联系方式","专业","签约时间	","企业顾问","人才顾问","个人业绩","建造师费用","企业证书费用","备注（打款或未打款）","挂资质/挂项目","QQ","微信","签约"};
	          
	        
	        HSSFCellStyle style = wb.createCellStyle();
	        style.setBorderBottom(BorderStyle.THIN);
	        style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderLeft(BorderStyle.THIN);
	        style.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderRight(BorderStyle.THIN);
	        style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
	        style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        
	        HSSFCellStyle cs2 = wb.createCellStyle();
	        cs2.setBorderBottom(BorderStyle.THIN);
	        cs2.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        cs2.setBorderLeft(BorderStyle.THIN);
	        cs2.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        cs2.setBorderRight(BorderStyle.THIN);
	        cs2.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        /*cs2.setBorderTop(BorderStyle.MEDIUM_DASHED); //虚线
	        cs2.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());*/
	   
	        // create 2 fonts objects
	        HSSFFont f = wb.createFont();
	        HSSFFont f2 = wb.createFont();

	        //set font 1 to 12 point type
	        f.setFontHeightInPoints((short) 12);
	        //make it red
	        f.setColor(HSSFColorPredefined.RED.getIndex());
	        // make it bold
	        //arial is the default font
	        f.setBold(true);

	        //set font 2 to 10 point type
	        f2.setFontHeightInPoints((short) 14);
	        //make it the color at palette index 0xf (white)
	        f2.setColor(HSSFColorPredefined.RED.getIndex());
	        //make it bold
	        f2.setBold(true);
	        
	        style.setFont(f2);
	        
	        int i =0;
	        int rows =0;
	        HSSFRow rowone = sheet.createRow(rows);
	        
	        for(String name:names){
	        	 HSSFCell cellone = rowone.createCell(i);
	 	        cellone.setCellValue(name);
	 	        cellone.setCellStyle(style);
	 	        sheet.setColumnWidth(i + 1, (int)((50 * 6) / ((double) 1 / 20)));
	        	i++;
	        }
	        int totalMoney = 0;//业绩总额
	        int myTotalMoney =0;//个人业绩总额
	        for(Contract  qyobj:qy){
	        	 rows++;
	        	 totalMoney+=qyobj.getServicecharge();
	        	 myTotalMoney += qyobj.getPermoney();		 
	        	 rowone = sheet.createRow(rows);
	        	 //签约时间
	        	 HSSFCell cellone = rowone.createCell(0);
		 	     cellone.setCellValue(qyobj.getSigningtime().substring(0,10));
		 	     cellone.setCellStyle(cs2);
		 	     //姓名
		 	     HSSFCell celltwo = rowone.createCell(1);
		 	     celltwo.setCellValue(qyobj.getCreateuserName());
		 	     celltwo.setCellStyle(cs2);
		 	     //签约企业
		 	     HSSFCell cellthr = rowone.createCell(2);
		 	     cellthr.setCellValue(qyobj.getCustomerInfo().getCname());
		 	     cellthr.setCellStyle(cs2);
		 	    //个人业绩
		 	     HSSFCell cellfou = rowone.createCell(3);
		 	    cellfou.setCellValue(qyobj.getPermoney());
		 	    cellfou.setCellStyle(cs2);
		 	    //总业绩
		 	     HSSFCell cellfiv = rowone.createCell(4);
		 	    cellfiv.setCellValue(qyobj.getServicecharge());
		 	    cellfiv.setCellStyle(cs2);
		 	    String remark ="";
		 	    List<ContractMoneylog>  mlogs = qyobj.getMlList();
		 	    for(ContractMoneylog mlog:mlogs){
		 	    	remark +=mlog.getCreatedate().substring(0,10)+ "回款"+mlog.getTotalamount()+"----"+mlog.getRemark()+" \n";
		 	    } 
		 	    //备注
		 	     HSSFCell cellsix = rowone.createCell(5);
		 	     cellsix.setCellValue(remark);
		 	     cellsix.setCellStyle(cs2);
		 	    //转入公司账户名称
		 	     HSSFCell cellsev = rowone.createCell(6);
		 	     cellsev.setCellValue("");
		 	     cellsev.setCellStyle(cs2);
		 	    //到账日期
		 	     HSSFCell celleig = rowone.createCell(7);
		 	    celleig.setCellValue(qyobj.getCreatedate().substring(0,10));
		 	    celleig.setCellStyle(cs2);
		 	    //单位打款账户名称
		 	    HSSFCell cellnig = rowone.createCell(8);
		 	     cellnig.setCellValue("");
		 	    cellnig.setCellStyle(cs2);
		 	    //是否开发票收据
		 	    HSSFCell cellten = rowone.createCell(9);
		 	    cellten.setCellValue(qyobj.getBill()==1?"已开":"未开");
		 	    cellten.setCellStyle(cs2);
		 	   //是否新签合同
		 	    HSSFCell celleve = rowone.createCell(10);
		 	    celleve.setCellValue(qyobj.getContractsize()>0?"续签":"新签");
		 	    celleve.setCellStyle(cs2);
	        }
	        //统计业绩总金额及个人业绩总金额
	        rows++;
	        rowone = sheet.createRow(rows);
	        HSSFCell celltotal = rowone.createCell(3);
	        celltotal.setCellValue(myTotalMoney);
	 	    celltotal = rowone.createCell(4);
	 	    celltotal.setCellValue(totalMoney);
	        
	        
	        i =0;
	        rows =0;
	        rowone = sheetone.createRow(rows);
	        for(String name:namestwo){
	        	 HSSFCell cellone = rowone.createCell(i);
	 	         cellone.setCellValue(name);
	 	         cellone.setCellStyle(style);
	 	        sheetone.setColumnWidth(i + 2, (int)((50 * 6) / ((double) 1 / 20)));
	        	 i++;
	        }
	      
	        myTotalMoney =0;//个人业绩总额
	 
	        for(Contract  rcobj:rc){
	        	
	        		if (rcobj.getCustomerInfo() != null) {
			          // if(rcobj.getSublist()!=null&&rcobj.getSublist().size()>0){
			        	   String key = rcobj.getId().toString();
			        	   myTotalMoney+=rcobj.getPermoney();
			        	   Map<String,Contract>  map = new HashMap<String,Contract>();
			        	   
			        	   rows++; 
			        	   rowone = sheetone.createRow(rows);
			        	   //单位名称
			        	   HSSFCell cellone = rowone.createCell(0);
				 	       cellone.setCellValue(rcobj.getParentContract().getCustomerInfo().getCname());
				 	       cellone.setCellStyle(cs2);
				 	    //	Contract   rcContract = rcobj.getSublist().get(0);
					 	     //所签建造师姓名
					 	     HSSFCell celltwo = rowone.createCell(1);
					 	     celltwo.setCellValue(rcobj.getCustomerInfo().getCname());
					 	    celltwo.setCellStyle(cs2);
					 	     //联系方式
					 	     HSSFCell cellthr = rowone.createCell(2);
					 	     cellthr.setCellValue(rcobj.getCustomerInfo().getTel());
					 	    cellthr.setCellStyle(cs2);
					 	    //专业
					 	     HSSFCell cellfou = rowone.createCell(3);
					 	    cellfou.setCellValue(rcobj.getCustomerInfo().getTechnicalpost());
					 	   cellfou.setCellStyle(cs2);
					 	    //签约时间
					 	     HSSFCell cellfiv = rowone.createCell(4);
					 	    cellfiv.setCellValue(rcobj.getCreatedate().substring(0,10));
					 	   cellfiv.setCellStyle(cs2);
					 	    //企业顾问
					 	     HSSFCell cellsix = rowone.createCell(5);
					 	     cellsix.setCellValue(rcobj.getParentContract().getCreateuserName());
					 	    cellsix.setCellStyle(cs2);
					 	    //人才顾问
					 	     HSSFCell cellsev = rowone.createCell(6);
					 	     cellsev.setCellValue(rcobj.getUpdateuserName());
					 	    cellsev.setCellStyle(cs2);
					 	    //服务费
					 	     HSSFCell celleig = rowone.createCell(7);
					 	    celleig.setCellValue(rcobj.getPermoney());
					 	   celleig.setCellStyle(cs2);
					 	    //建造师费用
					 	    HSSFCell cellnig = rowone.createCell(8);
					 	     cellnig.setCellValue(rcobj.getTotalamount());
					 	    cellnig.setCellStyle(cs2);
					 	   //企业建造师费用
					 	    HSSFCell celln = rowone.createCell(9);
					 	    if(rcobj.getAlonemoney()!=null){
					 	    	 celln.setCellValue(rcobj.getAlonemoney());
					 	    }
					 	    else{
					 	    	 celln.setCellValue(0);
					 	    } 
					 	    celln.setCellStyle(cs2);
					 	   //备注
					 	   String remark ="";
					 	    List<ContractMoneylog>  mlogs = rcobj.getMlList();
					 	    for(ContractMoneylog mlog:mlogs){
					 	    	remark +=mlog.getUpdatedate().substring(0,10)+ "向"+mlog.getName()+"打款"+mlog.getTotalamount()+"----"+mlog.getRemark()+" \n";
					 	    } 
					 	    
					 	    HSSFCell celleve = rowone.createCell(10);
					 	    celleve.setCellValue(remark);
					 	    celleve.setCellStyle(cs2); 
					 	  
					 	    List<ContractMoneylog>  cmLog =  rcobj.getMlList();
					 	    String result ="";
					 	    if(cmLog!=null&&cmLog.size()>0){
					 	    	result = cmLog.get(0).getUsetype()==0?"挂资质":"挂项目";
					 	    }
					 	    //挂资质/挂项目
					 	    HSSFCell celltwe = rowone.createCell(11);
					 	    celltwe.setCellValue(result);
					 	    celltwe.setCellStyle(cs2);
					 	    //QQ
					 	    HSSFCell cellthir = rowone.createCell(12);
					 	   cellthir.setCellValue(result);
					 	   cellthir.setCellValue(rcobj.getCustomerInfo().getQq());
					 	    //微信
						 	HSSFCell cellfourt = rowone.createCell(13);
						 	cellfourt.setCellValue(result);
						 	cellfourt.setCellValue(rcobj.getCustomerInfo().getWebchart());

						 	 //是否新签合同
					 	    HSSFCell cellfivt = rowone.createCell(14);
					 	   cellfivt.setCellStyle(cs2);
					 	    cellfivt.setCellValue(rcobj.getContractsize()>0?"续签":"新签");
					 	   

				 	   // }
	        		}
		        } 
	        
	        /*for(Contract  rcobj:rc){
	           if(rcobj.getSublist()!=null&&rcobj.getSublist().size()>0){
	        	   String key = rcobj.getId().toString();
	        	   myTotalMoney+=rcobj.getPermoney();
	        	   
	        	   rows++; 
	        	   rowone = sheetone.createRow(rows);
	        	 
	        	   //单位名称
	        	   HSSFCell cellone = rowone.createCell(0);
		 	       cellone.setCellValue(rcobj.getCustomerInfo().getCname());
		 	       cellone.setCellStyle(cs2);
		 	   
		 	    //	Contract   rcContract = rcobj.getSublist().get(0);
			 	     //所签建造师姓名
			 	     HSSFCell celltwo = rowone.createCell(6);
			 	     celltwo.setCellValue("");
			 	    celltwo.setCellStyle(cs2);
			 	     //联系方式
			 	     HSSFCell cellthr = rowone.createCell(7);
			 	     cellthr.setCellValue("");
			 	    cellthr.setCellStyle(cs2);
			 	    //专业
			 	     HSSFCell cellfou = rowone.createCell(8);
			 	    cellfou.setCellValue("");
			 	   cellfou.setCellStyle(cs2);
			 	    //签约时间
			 	     HSSFCell cellfiv = rowone.createCell(1);
			 	    cellfiv.setCellValue(rcobj.getCreatedate().substring(0,10));
			 	   cellfiv.setCellStyle(cs2);
			 	    //企业顾问
			 	     HSSFCell cellsix = rowone.createCell(2);
			 	     cellsix.setCellValue(rcobj.getCreateuserName());
			 	    cellsix.setCellStyle(cs2);
			 	    //人才顾问
			 	     HSSFCell cellsev = rowone.createCell(3);
			 	     cellsev.setCellValue(rcobj.getUpdateuserName());
			 	    cellsev.setCellStyle(cs2);
			 	    //服务费
			 	     HSSFCell celleig = rowone.createCell(4);
			 	    celleig.setCellValue(rcobj.getPermoney());
			 	   celleig.setCellStyle(cs2);
			 	    //建造师费用
			 	    HSSFCell cellnig = rowone.createCell(5);
			 	     cellnig.setCellValue("");
			 	    cellnig.setCellStyle(cs2);
			 	   //备注
			 	   String remark ="";
			 	    
			 	    HSSFCell celleve = rowone.createCell(9);
			 	    celleve.setCellValue(remark);
			 	    celleve.setCellStyle(cs2); 
			 	  
			 	    String result ="";
			 	    //挂资质/挂项目
			 	    HSSFCell cellten = rowone.createCell(10);
			 	    cellten.setCellValue(result);
			 	    cellten.setCellStyle(cs2);
	        	   
	        	   for(Contract   rcContract:rcobj.getSublist()){
	        		   //附加合同不算业绩
	        		   if(rcContract.getIsadditional()!=null&&rcContract.getIsadditional()==1){
	        			   continue;
	        		   } 
	        		   key = key+"-"+rcContract.getCustomerInfo().getId();
	        		  Long id =  map.get(key);
	        		  if(id==null){
	        			  map.put(key,rcContract.getCustomerInfo().getId());
	        		  }else{
	        			  continue;
	        		  }
	        		  
	        		  
	        	   }
	        	   
	        	  
		 	    }
		 	    
	        }*/
	        
	        //统计业绩总金额及个人业绩总金额
	        rows++;
	        rowone = sheetone.createRow(rows);
	        celltotal = rowone.createCell(7);
	        celltotal.setCellValue(myTotalMoney);
	        
	        ByteArrayOutputStream out = new ByteArrayOutputStream();  
	        wb.write(out);  
	        return out.toByteArray();  
	 }
	
	/* public static void main(String[] args) throws IOException {
	        HSSFWorkbook wb = new HSSFWorkbook();
	        HSSFSheet sheet = wb.createSheet("单位业绩表");
	        HSSFSheet sheetone = wb.createSheet("人才业绩表");
	        String names[] = new String[]{"签约时间","姓名","单位","个人业绩","总业绩","备注","转入公司账户名称","到账日期","单位打款账户名称","是否开发票收据"};
	        String namestwo[] = new String[]{"单位名称","所签建造师姓名","联系方式","专业","签约时间	","企业顾问","人才顾问","服务费	","建造师费用","备注（到账或未到账）","挂资质/挂项目"};
	          
	        
	        HSSFCellStyle style = wb.createCellStyle();
	        style.setBorderBottom(BorderStyle.THIN);
	        style.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderLeft(BorderStyle.THIN);
	        style.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderRight(BorderStyle.THIN);
	        style.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
	        style.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        
	        HSSFCellStyle cs2 = wb.createCellStyle();
	        cs2.setBorderBottom(BorderStyle.THIN);
	        cs2.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        cs2.setBorderLeft(BorderStyle.THIN);
	        cs2.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        cs2.setBorderRight(BorderStyle.THIN);
	        cs2.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());
	        cs2.setBorderTop(BorderStyle.MEDIUM_DASHED);
	        cs2.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());
	   
	        // create 2 fonts objects
	        HSSFFont f = wb.createFont();
	        HSSFFont f2 = wb.createFont();

	        //set font 1 to 12 point type
	        f.setFontHeightInPoints((short) 12);
	        //make it red
	        f.setColor(HSSFColorPredefined.RED.getIndex());
	        // make it bold
	        //arial is the default font
	        f.setBold(true);

	        //set font 2 to 10 point type
	        f2.setFontHeightInPoints((short) 14);
	        //make it the color at palette index 0xf (white)
	        f2.setColor(HSSFColorPredefined.RED.getIndex());
	        //make it bold
	        f2.setBold(true);
	        
	        
	        style.setFont(f2);
	        
	        
	        HSSFRow rowone = sheet.createRow(0);
	        int i =0;
	        for(String name:names){
	        	 HSSFCell cellone = rowone.createCell(i);
	 	        cellone.setCellValue(name);
	 	        cellone.setCellStyle(style);
	 	        sheet.setColumnWidth(i + 1, (int)((50 * 6) / ((double) 1 / 20)));
	        	i++;
	        }
	        
	       
	        
	        rowone = sheetone.createRow(0);
	        i =0;
	        for(String name:namestwo){
	        	 HSSFCell cellone = rowone.createCell(i);
	 	         cellone.setCellValue(name);
	 	         cellone.setCellStyle(style);
	 	        sheetone.setColumnWidth(i + 2, (int)((50 * 6) / ((double) 1 / 20)));
	        	 i++;
	        }
	        
	        
	      
	       

	       
	        
	        HSSFRow row = sheet.createRow(1);
	        
	        HSSFCell cell = row.createCell(1);
	        cell.setCellValue(new Date());

	        // we style the second cell as a date (and time).  It is important to create a new cell style from the workbook
	        // otherwise you can end up modifying the built in style and effecting not only this cell but other cells.
	        HSSFCellStyle cellStyle = wb.createCellStyle();
	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm"));
	        cell = row.createCell(1);
	        cell.setCellValue(new Date());
	        cell.setCellStyle(cellStyle);
	        
	        
	        // create with default name
	        wb.createSheet();
	        final String name = "人才业绩表";
	        // setting sheet name later
	        wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name));
	        
	        
	        
	        FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");
	        wb.write(fileOut);
	        fileOut.close();
	        wb.close();
	    }*/
	 
	 public static void main(String[] args) {
		 readExcelS()  ;
	}
	 

	 public static void readExcelS()  
	 {  
	     SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
	     try {  
	     
	         //同时支持Excel 2003、2007  
	         File excelFile = new File("D://2016.11.07.xls"); //创建文件对象  
	         FileInputStream is = new FileInputStream(excelFile); //文件流  
	         
	         Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的 
	         int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量  
	         //遍历每个Sheet  
	         for (int s = 0; s < sheetCount; s++) {  
	             Sheet sheet = workbook.getSheetAt(s);  
	             int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数  
	             String title="";
	             String titles[] = null;
	             //遍历每一行  
	             for (int r = 0; r < rowCount; r++) {  
	            	
	            	 
	                 Row row = sheet.getRow(r);  
	                 int cellCount = row.getPhysicalNumberOfCells(); //获取总列数  
	                 
	                  if(r==1){
	                	  titles = new String[cellCount];
		              }
	                 
	                 //遍历每一列  
	                 for (int c = 0; c < cellCount; c++) {  
	                     Cell cell = row.getCell(c);  
	                     int cellType = cell.getCellType();  
	                     String cellValue = null;  
	                     switch(cellType) {  
	                         case Cell.CELL_TYPE_STRING: //文本  
	                             cellValue = cell.getStringCellValue();  
	                             break;  
	                         case Cell.CELL_TYPE_NUMERIC: //数字、日期  
	                             if(DateUtil.isCellDateFormatted(cell)) {  
	                                 cellValue = fmt.format(cell.getDateCellValue()); //日期型  
	                             }  
	                             else {  
	                            	 DecimalFormat df = new DecimalFormat("#");
	                                 cellValue = df.format(cell.getNumericCellValue()); //数字
	                             }  
	                             break;  
	                         case Cell.CELL_TYPE_BOOLEAN: //布尔型  
	                             cellValue = String.valueOf(cell.getBooleanCellValue());  
	                             break;  
	                         case Cell.CELL_TYPE_BLANK: //空白  
	                             cellValue = cell.getStringCellValue();  
	                             break;  
	                         case Cell.CELL_TYPE_ERROR: //错误  
	                             cellValue = "错误";  
	                             break;  
	                         case Cell.CELL_TYPE_FORMULA: //公式  
	                             cellValue = "错误";  
	                             break;  
	                         default:  
	                             cellValue = "错误";  
	                     }  
	                     if(r==1){
	                    	 titles[c]=cellValue;
	                     }
	                     if(r>1){
	                    	  System.out.print( titles[c]+":"+cellValue + "    ");  
	                     }
	                   
	                 }  
	                 System.out.println();  
	             }  
	         }  
	   
	     }  
	     catch (Exception e) {  
	         e.printStackTrace();  
	     }  
	   
	    // return Action.SUCCESS;  
	 }  
	 
	 public static void readExcel()  
	 {  
	     SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
	     try {  
	     
	         //同时支持Excel 2003、2007  
	         File excelFile = new File("D://2016.11.07.xls"); //创建文件对象  
	         FileInputStream is = new FileInputStream(excelFile); //文件流  
	         
	         Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的 
	         int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量  
	         //遍历每个Sheet  
	         for (int s = 0; s < sheetCount; s++) {  
	             Sheet sheet = workbook.getSheetAt(s);  
	             int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数  
	             //遍历每一行  
	             for (int r = 0; r < rowCount; r++) {  
	                 Row row = sheet.getRow(r);  
	                 int cellCount = row.getPhysicalNumberOfCells(); //获取总列数  
	                 //遍历每一列  
	                 for (int c = 0; c < cellCount; c++) {  
	                     Cell cell = row.getCell(c);  
	                     int cellType = cell.getCellType();  
	                     String cellValue = null;  
	                     switch(cellType) {  
	                         case Cell.CELL_TYPE_STRING: //文本  
	                             cellValue = cell.getStringCellValue();  
	                             break;  
	                         case Cell.CELL_TYPE_NUMERIC: //数字、日期  
	                             if(DateUtil.isCellDateFormatted(cell)) {  
	                                 cellValue = fmt.format(cell.getDateCellValue()); //日期型  
	                             }  
	                             else {  
	                                 DecimalFormat df = new DecimalFormat("#");
	                                 cellValue = df.format(cell.getNumericCellValue()); //数字
	                             }  
	                             break;  
	                         case Cell.CELL_TYPE_BOOLEAN: //布尔型  
	                             cellValue = String.valueOf(cell.getBooleanCellValue());  
	                             break;  
	                         case Cell.CELL_TYPE_BLANK: //空白  
	                             cellValue = cell.getStringCellValue();  
	                             break;  
	                         case Cell.CELL_TYPE_ERROR: //错误  
	                             cellValue = "错误";  
	                             break;  
	                         case Cell.CELL_TYPE_FORMULA: //公式  
	                             cellValue = "错误";  
	                             break;  
	                         default:  
	                             cellValue = "错误";  
	                     }  
	                     System.out.print(cellValue + "    ");  
	                 }  
	                 System.out.println();  
	             }  
	         }  
	   
	     }  
	     catch (Exception e) {  
	         e.printStackTrace();  
	     }  
	   
	    // return Action.SUCCESS;  
	 }  
}
